[MySQL] Optimize Query

Posted by bordeux on Stack Overflow See other posts from Stack Overflow or by bordeux
Published on 2011-01-15T17:13:05Z Indexed on 2011/01/15 17:53 UTC
Read the original article Hit count: 125

Filed under:
|
|
|

Hello. I have problem with optimize this query:

SET @SEARCH = "dokumentalne";

SELECT SQL_NO_CACHE
`AA`.`version` AS `Version` , 
`AA`.`contents` AS `Contents` , 
`AA`.`idarticle` AS `AdressInSQL` , 
`AA` .`topic` AS `Topic` ,
MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH) AS `Relevance` , 
`IA`.`url` AS `URL`
FROM `xv_article` AS `AA`
INNER JOIN `xv_articleindex` AS `IA` ON ( `AA`.`idarticle` = `IA`.`adressinsql` )
INNER JOIN (
    SELECT `idarticle` , MAX( `version` ) AS `version`
    FROM `xv_article`
    WHERE MATCH (`topic` , `contents`) AGAINST (@SEARCH)
    GROUP BY `idarticle`
) AS `MG`
ON ( `AA`.`idarticle` = `MG`.`idarticle` ) 
WHERE `IA`.`accepted` = "yes"
AND `AA`.`version` = `MG`.`version`
ORDER BY `Relevance` DESC
LIMIT 0 , 30

Now, this query using ^ 20 seconds. How to optimize this?

EXPLAIN gives this:

1   PRIMARY     AA  ALL     NULL    NULL    NULL    NULL    11169   Using temporary; Using filesort
1   PRIMARY       ALL     NULL    NULL    NULL    NULL    681     Using where
1   PRIMARY     IA  ALL     accepted    NULL    NULL    NULL    11967   Using where
2   DERIVED     xv_article  fulltext    topic   topic   0       1   Using where; Using temporary; Using filesort

This is example server with my data:

user: bordeux_4prog
password: 4prog
phpmyadmin: http://phpmyadmin.bordeux.net/
chive: http://chive.bordeux.net/

© Stack Overflow or respective owner

Related posts about mysql

Related posts about match